package com.tangula.web.kashine.repository;



import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.tangula.web.kashine.entity.AccountEntity;

@Repository
public interface AccountEntityRepository extends JpaRepository<AccountEntity, String>{

	AccountEntity findByEmail(String email);

	List<AccountEntity> findByParentOrgOfTeacherAndTypeAndBlock(String orgid,String type,String block,Pageable pageable);
	

	@Query(nativeQuery=true,value="SELECT a.* FROM t_account a,t_studentorganizationref s WHERE s.org_id= :orgId AND a.block='y' AND a.id = s.student_id limit :index , :size")
	List<AccountEntity> loadStudentOfOrg(@Param("orgId")String orgId,@Param("index")int index,@Param("size")int size);

	String findParentOrgOfTeacherById(String teacherId);

	String findNameById(String studentId);

	List<AccountEntity> findByParentOrgOfTeacher(String orgId);

	List<AccountEntity> findByParentOrgOfTeacherAndBlock(String orgId, String string);

	List<AccountEntity> findByParentOrgOfTeacherAndTypeAndBlock(String orgid,String type,String block);
	@Query(nativeQuery=true,value="SELECT a.* FROM t_account a,t_studentorganizationref s WHERE s.org_id= :orgId AND a.block='y' AND a.id = s.student_id")
	List<AccountEntity> loadStudentOfOrg(@Param("orgId")String orgId);

	List<AccountEntity> findByType(String string);
	
	Page<AccountEntity> findByType(String string, Pageable pagination);
	

	List<AccountEntity> findByTypeAndBlock(String string, String string2, Pageable of);
	
	int countByTypeAndBlock(String type, String block);

	List<AccountEntity> findByTypeAndBlock(String string, String string2);

	@Query(nativeQuery=true, value="select distinct * from t_account t  where t.id not in (select r.student_id from t_studentorganizationref r where r.org_id=:orgId) and t.block='y' AND type = 'STUDENT'")
	List<AccountEntity> findByStudentsOutOfOrg(@Param("orgId") String orgId, Pageable page);
	
	@Query(nativeQuery=true,  countName="id", value="select count(*) from t_account t  where t.id not in (select r.student_id from r_org_student r where r.org_id=:orgId) and t.block='y' and type='STUDENT' ")
	int countByStudentsOutOfOrg(@Param("orgId") String orgId);
	
	
	@Query(nativeQuery=true, value="  select * from t_account t inner join t_studentorganizationref r on (t.id = r.student_id) where r.org_id = :orgId AND type = 'STUDENT'")
	List<AccountEntity> findOrgStudents(@Param("orgId") String orgId, Pageable page);
	

	@Query(nativeQuery=true, value="  select count(*) from t_account t inner join t_studentorganizationref r on (t.id = r.student_id) where r.org_id = :orgId")
	int countOrgStudents(@Param("orgId") String orgId);

	int countByParentOrgOfTeacher(String id);

	@Query(nativeQuery=true, value="select stu.* from t_account stu inner join  r_org_student ros on (ros.student_id = stu.id)\r\n" +
			"  inner join t_account ta on ros.org_id = ta.parent_org_of_teacher where stu.type='STUDENT' and ta.id=:teacherId \r\n" +
			"  and ros.student_id not in \r\n" +
			"  (select csr.student_id from t_classstudentref csr where csr.class_id=:classId)")
	Page<AccountEntity> findOutofClassStudent(
			@Param("teacherId")String teacherId, @Param("classId")String classId, Pageable pagination);
	@Query(nativeQuery=true, value="SELECT a.NAME AS cost FROM t_history h, t_account a WHERE h.resave_id = :teacherId AND h.user_id = a.id GROUP BY h.user_id ORDER BY SUM(h.money) DESC")
	List<String> findTopSpenderName(@Param("teacherId")String teacherId);
	@Query(nativeQuery=true, value="SELECT a.`name` FROM t_studentaward s,t_account a WHERE s.student_id = a.id AND s.owner_teacher_id = :teacherId GROUP BY s.student_id ORDER BY COUNT(s.student_id) DESC ")
	List<String> findTopEarnerNames(@Param("teacherId")String teacherId);
	@Query(nativeQuery=true, value="	SELECT a.* FROM t_account a,t_class c,(SELECT cs.* FROM t_classstudentref cs WHERE cs.student_id = :studentId) cs WHERE a.id = c.owner_teacher AND c.id = cs.class_id")
	List<AccountEntity> loadAllTeacherOfStudent(@Param("studentId") String student);

	@Query(nativeQuery=true, value="  select * from t_account t inner join (\r\n" + 
			"  select distinct student_id from t_classstudentref r inner join t_class c on (r.class_id = c.id) where c.owner_teacher=:teacherId " + 
			"  ) x on (t.id = x.student_id)")
	Page<AccountEntity> queryAllStudentOfTeacher(@Param("teacherId") String teacherId, Pageable pr);

	
	@Query(nativeQuery=true,value="SELECT a.* FROM r_org_student r,t_account a  WHERE r.org_id = :orgId AND r.student_id = a.id AND a.block = 'y' LIMIT   :index , :size")
	List<AccountEntity> findByOrgId(@Param("orgId")String orgId, @Param("index")int dex,@Param("size")int size);

	
	
	

}
